import Database from 'better-sqlite3'
import { app } from 'electron'

const userDataPath = app.getPath('userData')
const fs = require('fs').promises
const path = require('path')
const dirPath = path.join(__dirname, '../../resources/data').replace("app.asar", "app.asar.unpacked") // 示例目录路径
createDb()
const db = new Database(`${dirPath}/bilu.db`)
export function initDb() {
  createUserTable()
  createXwblTable()
  createJtcyTable()
}

function createDb() {
  fs.mkdir(dirPath, { recursive: true }) // 使用 { recursive: true } 可以创建多级目录
    .then(() => {
      console.log('create dir success')
    })
    .catch((err) => {
      if (err.code !== 'EEXIST') {
        // 如果目录已存在，会抛出 EEXIST 错误
        console.error('create dir failed', err)
      } else {
        console.log('exist')
      }
    })
}

export function select(sql,...params){
  const stmt = db.prepare(sql);
  if(params.length>0){
    return stmt.all(params)
  }else{
    return stmt.all()
  }
}

export function update(sql,...params){
  const stmt = db.prepare(sql);
  if(params.length>0){
    return stmt.run(params)
  }else{
    return stmt.run()
  }
}

//创建用户表
function createUserTable() {
  const tableName = 'user'
  const createTableSql = `CREATE TABLE IF NOT EXISTS ${tableName} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL
  );`
  const insertSql = `INSERT INTO ${tableName} (username, password) VALUES ('admin', 'admin');`
  createTable(tableName, createTableSql,insertSql)
}

//创建询问笔录表
function createXwblTable() {
  const tableName = 'bl_xw'
  const createTableSql = `CREATE TABLE IF NOT EXISTS ${tableName} (id INTEGER PRIMARY KEY AUTOINCREMENT, cs INTEGER, blgs TEXT, rylx TEXT, ajlx TEXT, rytz TEXT, ajbh TEXT, dafs TEXT, kssj DATETIME, jssj DATETIME, dd TEXT, zjlx TEXT, zjhm TEXT, bxwr TEXT, cym TEXT, xb TEXT, crrq DATE, whcd TEXT, nl INTEGER, gj TEXT, mz TEXT, zzmm TEXT, lxdh TEXT, hjzz TEXT, xzz TEXT, gzdw TEXT, sfgz TEXT, blnr TEXT,jlr TEXT,jlrgzdw TEXT,xwr TEXT,xwrgzdw TEXT);`
  const insertSql = null
  createTable(tableName, createTableSql,insertSql)
}

//创建家庭成员表
function createJtcyTable() {
  const tableName = 'bl_jtcy'
  const createTableSql = `CREATE TABLE IF NOT EXISTS ${tableName} (id INTEGER, blid INTEGER, sftz TEXT, xm TEXT, gx TEXT, nl INTEGER, dh TEXT, dwhzz TEXT);`
  const insertSql = null
  createTable(tableName, createTableSql,insertSql)
}

function createTable(tableName, createTableSql,insertSql) {
  // 检查表是否存在
  const masterStmt = db.prepare('SELECT * FROM sqlite_master WHERE type=? AND name = ?')
  let tableExists = masterStmt.get('table', tableName)
  if (!tableExists) {
    // 创建表
    db.exec(createTableSql)
    if(insertSql){
      db.exec(insertSql)
    }
    console.log(`create ${tableName} table success`)
  }else{
    console.log(`${tableName} table exists`)
  }
}


